using System.Linq;
using System.Text;
using System.Diagnostics;
using System.Runtime.Serialization;
using System.Collections;
using System.Data;
using System.ComponentModel;
using System.Data.OracleClient;
using WPFExcelReport.Helpers;
using WPFExcelReport.Controls;
namespace WPFExcelReport
{
    public partial class VFSAMVP
    {
        public DataRow Convert2FSAMVP(DataRow dwInput, string ITXDBVERSION, bool IsConvert = false)
        {
            DataSet SResult = new DataSet();
            SResult = QueryFSAMVP(dwInput, 0);
            DataRow dwReturn = null;
            if (SResult.Tables[0].Rows.Count > 0)
            {
                dwReturn = SResult.Tables[0].Rows[0];
            }
            else
            {
                dwReturn = SResult.Tables[0].NewRow();
            }
            switch (ITXDBVERSION)
            {
                case "YGR":
                    #region=========
         dwReturn["FSAMANDANT"] = " ".PadRight(2 ); 
         dwReturn["FSAART"] = " ".PadRight(20 ); 
         dwReturn["FSAVAR"] = " ".PadRight(2 ); 
         dwReturn["FSAFARBE"] = " ".PadRight(10 ); 
         dwReturn["FSAGROE"] = " ".PadRight(7 ); 
         dwReturn["FSAAUFM"] = " ".PadRight(2 ); 
         dwReturn["FSAVERP"] = " ".PadRight(3 ); 
         dwReturn["FSAAENZL"] = " ".PadRight(0 ); 
         dwReturn["FSAAENB"] = " ".PadRight(8 ); 
         dwReturn["FSAAEND"] = " ".PadRight(0 ); 
         dwReturn["FSANEUB"] = " ".PadRight(8 ); 
         dwReturn["FSANEUD"] = " ".PadRight(0 ); 
         dwReturn["FSAKUNDNR"] = " ".PadRight(8 ); 
         dwReturn["FSALAENGE"] = " ".PadRight(0 ); 
         dwReturn["FSABREITE"] = " ".PadRight(0 ); 
         dwReturn["FSAHOEHE"] = " ".PadRight(0 ); 
         dwReturn["FSALBHME"] = " ".PadRight(3 ); 
         dwReturn["FSALBHGRP"] = " ".PadRight(1 ); 
         dwReturn["FSALBHFKT"] = " ".PadRight(12 ); 
         dwReturn["FSAGEWZU"] = " ".PadRight(0 ); 
         dwReturn["FSAVPIVP"] = " ".PadRight(4 ); 
         dwReturn["FSAENTSKL1"] = " ".PadRight(4 ); 
         dwReturn["FSAANTPR1"] = " ".PadRight(0 ); 
         dwReturn["FSAENTSKL2"] = " ".PadRight(4 ); 
         dwReturn["FSAANTPR2"] = " ".PadRight(0 ); 
         dwReturn["FSAENTSKL3"] = " ".PadRight(4 ); 
         dwReturn["FSAANTPR3"] = " ".PadRight(0 ); 
         dwReturn["FSAENTSKL4"] = " ".PadRight(4 ); 
         dwReturn["FSAANTPR4"] = " ".PadRight(0 ); 
         dwReturn["FSAENTSKL5"] = " ".PadRight(4 ); 
         dwReturn["FSAANTPR5"] = " ".PadRight(0 ); 
         dwReturn["FSAETIKETT1"] = " ".PadRight(3 ); 
         dwReturn["FSAETIKETT2"] = " ".PadRight(3 ); 
         dwReturn["FSAETIKETT3"] = " ".PadRight(3 ); 
         dwReturn["FSAPRGRP"] = " ".PadRight(1 ); 
         dwReturn["FSAANZVP"] = " ".PadRight(0 ); 
         dwReturn["FSAZUABVH"] = " ".PadRight(0 ); 
         dwReturn["FSAZUABAK"] = " ".PadRight(0 ); 
         dwReturn["FSAZUABKU"] = " ".PadRight(0 ); 
         dwReturn["FSAZUABPR"] = " ".PadRight(1 ); 
         dwReturn["FSAFILLER"] = " ".PadRight(31 ); 
                    #endregion
                    break;
                case "JLT":

                    break;
                default:

                    break;
            }
            if (IsConvert)
            {
                foreach (DataColumn cm in dwInput.Table.Columns)
                {
                    if (dwInput[cm.ColumnName].ToString() == "")
                        continue;
                    if (dwReturn.Table.Columns.Contains(cm.ColumnName))
                    {
                        dwReturn[cm.ColumnName] = dwInput[cm.ColumnName];
                    }
                }
            }
            return dwReturn;
        }
        public void MapFSAMVP(ref DataRow dwLocal, DataRow dwData)
        {
            BlindMap(ref dwLocal, dwData);
        }
        public void SaveFSAMVP(DataRow dwData, string ITXDBVERSION = "50.24")
        {
            DataSet setResult = new DataSet();
            DataTable tbLocal = null;
            DataRow dwLocal = null;
            bool flgExist = false;
            setResult = QueryFSAMVP(dwData);
            if (setResult.Tables.Count > 0)
            {
                tbLocal = setResult.Tables[0];
                if (tbLocal.Rows.Count > 0)
                {
                    flgExist = true;
                    dwLocal = tbLocal.Rows[0];
                }
                else
                {
                    dwLocal = tbLocal.NewRow();
                }
            }
            MapFSAMVP(ref dwLocal, dwData);

            if (!flgExist)
            {

                InsertFSAMVP(dwLocal, ITXDBVERSION);
            }
            UpdateFSAMVP(dwLocal);
        }
        public void InsertFSAMVP(DataRow dwData, string ITXDBVERSION = "50.24")
        {
            OracleCommand or1;
            or1 = OracleDB.CreateCommand();
            switch (ITXDBVERSION)
            {
                case "YGR":
                    or1.CommandText = "INSERT INTO FSAMVP(VAKBMANDANT, VAKBKOLL, VAKBART, VAKBKUNDNR, VAKBFARBE, VAKBKDARTNR, VAKBKDARTBEZ, VAKBKDFARBNR, VAKBANDRUCK, VAKBMEGEW, VAKBMENGEH, VAKBGEWVON, VAKBGEWBIS, VAKBGEWCA, VAKBGEWFKT, VAKBGEWGRP, VAKBMEGFKT, VAKBMEGGRP, VAKBAENDZL, VAKBAENDBEN, VAKBAENDDAT, VAKBNEUBEN, VAKBNEUDAT, VAKBFILLER)"
                        + " VALUES (:VAKBMANDANT, :VAKBKOLL, :VAKBART, :VAKBKUNDNR, :VAKBFARBE, '                         ', '                         ', '                             ', ' ', '   ', '   ', 0, 0, ' ', '000000000000', ' ', '000000000000', ' ', 0, '        ', '000000', '        ', '      ', '                                          ')";
                    or1.Parameters.Add("VAKBMANDANT", OracleType.NVarChar).Value = dwData["VAKBMANDANT"].ToString();
                    or1.Parameters.Add("VAKBKOLL", OracleType.NVarChar).Value = dwData["VAKBKOLL"].ToString();
                    or1.Parameters.Add("VAKBART", OracleType.NVarChar).Value = dwData["VAKBART"].ToString();
                    or1.Parameters.Add("VAKBFARBE", OracleType.NVarChar).Value = dwData["VAKBFARBE"].ToString();
                    or1.Parameters.Add("VAKBKUNDNR", OracleType.NVarChar).Value = dwData["VAKBKUNDNR"].ToString();
                    break;
                //VAFMANDANT VAFKOLL VAFART VAFFARBE VAFBEZ VAFFFARBE

            }
            
            Connect2Proxy(or1);
        }
        public DataSet QueryFSAMVP(DataRow dwData, int option = 1)
        {
            DataSet Return;
            DataSet Result = new DataSet();
            OracleCommand or1 = OracleDB.CreateCommand();
            switch (option)
            {
                case 0:
                    or1.CommandText = "SELECT * FROM FSAMVP WHERE  ROWNUM<2 ";
                    break;
              
                case 99:
                    or1.CommandText = "SELECT * FROM FSAMVP  WHERE ROWNUM<50";
                    break;
                default:
                    break;
            }
            Result = Connect2Proxy(or1);
            if (Result.Tables.Count > 0)
                Result.Tables[0].TableName = "FSAMVP";
            Return = Result;
            return Return;
        }
        public void DeleteFSAMVP(DataRow dwData, int option = 1)
        {

            OracleCommand or1 = OracleDB.CreateCommand();
            switch (option)
            {
                case 1:
                    or1.CommandText = "DELETE FROM FSAMVP   WHERE  VAKBMANDANT=:VAKBMANDANT AND VAKBKOLL=:VAKBKOLL AND VAKBART =:VAKBART AND VAKBFARBE=:VAKBFARBE AND VAKBKUNDNR=:VAKBKUNDNR";
                     or1.Parameters.Add("VAKBMANDANT", OracleType.NVarChar).Value = dwData["VAKBMANDANT"].ToString();
                    or1.Parameters.Add("VAKBKOLL", OracleType.NVarChar).Value = dwData["VAKBKOLL"].ToString();
                    or1.Parameters.Add("VAKBART", OracleType.NVarChar).Value = dwData["VAKBART"].ToString();
                    or1.Parameters.Add("VAKBFARBE", OracleType.NVarChar).Value = dwData["VAKBFARBE"].ToString();
                    or1.Parameters.Add("VAKBKUNDNR", OracleType.NVarChar).Value = dwData["VAKBKUNDNR"].ToString();
                    break;
                default:
                    break;
            }
            Connect2Proxy(or1);
        } 
      
        public void UpdateFSAMVP(DataRow dwData, string ITXDBVERSION = "50.24", int option = 1)
        {
            OracleCommand or1;
            DataSet Result = new DataSet();
            or1 = OracleDB.CreateCommand();
            switch (option)
            {
                case 1:
                    #region====Command==== 
                    or1.CommandText = "UPDATE FSAMVP SET "
                    +"FSAMANDANT= :FSAMANDANT,"
                    +"FSAART= :FSAART,"
                    +"FSAVAR= :FSAVAR,"
                    +"FSAFARBE= :FSAFARBE,"
                    +"FSAGROE= :FSAGROE,"
                    +"FSAAUFM= :FSAAUFM,"
                    +"FSAVERP= :FSAVERP,"
                    +"FSAAENZL= :FSAAENZL,"
                    +"FSAAENB= :FSAAENB,"
                    +"FSAAEND= :FSAAEND,"
                    +"FSANEUB= :FSANEUB,"
                    +"FSANEUD= :FSANEUD,"
                    +"FSAKUNDNR= :FSAKUNDNR,"
                    +"FSALAENGE= :FSALAENGE,"
                    +"FSABREITE= :FSABREITE,"
                    +"FSAHOEHE= :FSAHOEHE,"
                    +"FSALBHME= :FSALBHME,"
                    +"FSALBHGRP= :FSALBHGRP,"
                    +"FSALBHFKT= :FSALBHFKT,"
                    +"FSAGEWZU= :FSAGEWZU,"
                    +"FSAVPIVP= :FSAVPIVP,"
                    +"FSAENTSKL1= :FSAENTSKL1,"
                    +"FSAANTPR1= :FSAANTPR1,"
                    +"FSAENTSKL2= :FSAENTSKL2,"
                    +"FSAANTPR2= :FSAANTPR2,"
                    +"FSAENTSKL3= :FSAENTSKL3,"
                    +"FSAANTPR3= :FSAANTPR3,"
                    +"FSAENTSKL4= :FSAENTSKL4,"
                    +"FSAANTPR4= :FSAANTPR4,"
                    +"FSAENTSKL5= :FSAENTSKL5,"
                    +"FSAANTPR5= :FSAANTPR5,"
                    +"FSAETIKETT1= :FSAETIKETT1,"
                    +"FSAETIKETT2= :FSAETIKETT2,"
                    +"FSAETIKETT3= :FSAETIKETT3,"
                    +"FSAPRGRP= :FSAPRGRP,"
                    +"FSAANZVP= :FSAANZVP,"
                    +"FSAZUABVH= :FSAZUABVH,"
                    +"FSAZUABAK= :FSAZUABAK,"
                    +"FSAZUABKU= :FSAZUABKU,"
                    +"FSAZUABPR= :FSAZUABPR,"
                    +"FSAFILLER= :FSAFILLER,"
                        + " WHERE  "
                        + "  VAKBMANDANT=:VAKBMANDANT AND VAKBKOLL=:VAKBKOLL AND VAKBART =:VAKBART AND VAKBFARBE=:VAKBFARBE AND VAKBKUNDNR=:VAKBKUNDNR";
                    #endregion

                    #region====Parameters====
          or1.Parameters.Add("FSAMANDANT", OracleType.NVarChar).Value = dwData["FSAMANDANT"].ToString(); 
          or1.Parameters.Add("FSAART", OracleType.NVarChar).Value = dwData["FSAART"].ToString(); 
          or1.Parameters.Add("FSAVAR", OracleType.NVarChar).Value = dwData["FSAVAR"].ToString(); 
          or1.Parameters.Add("FSAFARBE", OracleType.NVarChar).Value = dwData["FSAFARBE"].ToString(); 
          or1.Parameters.Add("FSAGROE", OracleType.NVarChar).Value = dwData["FSAGROE"].ToString(); 
          or1.Parameters.Add("FSAAUFM", OracleType.NVarChar).Value = dwData["FSAAUFM"].ToString(); 
          or1.Parameters.Add("FSAVERP", OracleType.NVarChar).Value = dwData["FSAVERP"].ToString(); 
          or1.Parameters.Add("FSAAENZL", OracleType.NVarChar).Value = dwData["FSAAENZL"].ToString(); 
          or1.Parameters.Add("FSAAENB", OracleType.NVarChar).Value = dwData["FSAAENB"].ToString(); 
          or1.Parameters.Add("FSAAEND", OracleType.NVarChar).Value = dwData["FSAAEND"].ToString(); 
          or1.Parameters.Add("FSANEUB", OracleType.NVarChar).Value = dwData["FSANEUB"].ToString(); 
          or1.Parameters.Add("FSANEUD", OracleType.NVarChar).Value = dwData["FSANEUD"].ToString(); 
          or1.Parameters.Add("FSAKUNDNR", OracleType.NVarChar).Value = dwData["FSAKUNDNR"].ToString(); 
          or1.Parameters.Add("FSALAENGE", OracleType.NVarChar).Value = dwData["FSALAENGE"].ToString(); 
          or1.Parameters.Add("FSABREITE", OracleType.NVarChar).Value = dwData["FSABREITE"].ToString(); 
          or1.Parameters.Add("FSAHOEHE", OracleType.NVarChar).Value = dwData["FSAHOEHE"].ToString(); 
          or1.Parameters.Add("FSALBHME", OracleType.NVarChar).Value = dwData["FSALBHME"].ToString(); 
          or1.Parameters.Add("FSALBHGRP", OracleType.NVarChar).Value = dwData["FSALBHGRP"].ToString(); 
          or1.Parameters.Add("FSALBHFKT", OracleType.NVarChar).Value = dwData["FSALBHFKT"].ToString(); 
          or1.Parameters.Add("FSAGEWZU", OracleType.NVarChar).Value = dwData["FSAGEWZU"].ToString(); 
          or1.Parameters.Add("FSAVPIVP", OracleType.NVarChar).Value = dwData["FSAVPIVP"].ToString(); 
          or1.Parameters.Add("FSAENTSKL1", OracleType.NVarChar).Value = dwData["FSAENTSKL1"].ToString(); 
          or1.Parameters.Add("FSAANTPR1", OracleType.NVarChar).Value = dwData["FSAANTPR1"].ToString(); 
          or1.Parameters.Add("FSAENTSKL2", OracleType.NVarChar).Value = dwData["FSAENTSKL2"].ToString(); 
          or1.Parameters.Add("FSAANTPR2", OracleType.NVarChar).Value = dwData["FSAANTPR2"].ToString(); 
          or1.Parameters.Add("FSAENTSKL3", OracleType.NVarChar).Value = dwData["FSAENTSKL3"].ToString(); 
          or1.Parameters.Add("FSAANTPR3", OracleType.NVarChar).Value = dwData["FSAANTPR3"].ToString(); 
          or1.Parameters.Add("FSAENTSKL4", OracleType.NVarChar).Value = dwData["FSAENTSKL4"].ToString(); 
          or1.Parameters.Add("FSAANTPR4", OracleType.NVarChar).Value = dwData["FSAANTPR4"].ToString(); 
          or1.Parameters.Add("FSAENTSKL5", OracleType.NVarChar).Value = dwData["FSAENTSKL5"].ToString(); 
          or1.Parameters.Add("FSAANTPR5", OracleType.NVarChar).Value = dwData["FSAANTPR5"].ToString(); 
          or1.Parameters.Add("FSAETIKETT1", OracleType.NVarChar).Value = dwData["FSAETIKETT1"].ToString(); 
          or1.Parameters.Add("FSAETIKETT2", OracleType.NVarChar).Value = dwData["FSAETIKETT2"].ToString(); 
          or1.Parameters.Add("FSAETIKETT3", OracleType.NVarChar).Value = dwData["FSAETIKETT3"].ToString(); 
          or1.Parameters.Add("FSAPRGRP", OracleType.NVarChar).Value = dwData["FSAPRGRP"].ToString(); 
          or1.Parameters.Add("FSAANZVP", OracleType.NVarChar).Value = dwData["FSAANZVP"].ToString(); 
          or1.Parameters.Add("FSAZUABVH", OracleType.NVarChar).Value = dwData["FSAZUABVH"].ToString(); 
          or1.Parameters.Add("FSAZUABAK", OracleType.NVarChar).Value = dwData["FSAZUABAK"].ToString(); 
          or1.Parameters.Add("FSAZUABKU", OracleType.NVarChar).Value = dwData["FSAZUABKU"].ToString(); 
          or1.Parameters.Add("FSAZUABPR", OracleType.NVarChar).Value = dwData["FSAZUABPR"].ToString(); 
          or1.Parameters.Add("FSAFILLER", OracleType.NVarChar).Value = dwData["FSAFILLER"].ToString(); 
                    #endregion
                    break; 
                default:
                    break;
            }


            Connect2Proxy(or1);
        }
    }
}
